/*******************************************************************************
 *
 * File Name:          UsersDAO.java
 *
 * Created:            2006/10/03 02:48:11
 *
 * Original Author:    njo soft
 *
 *------------------------------------------------------------------------------
 * MODIFICATION HISTORY
 *------------------------------------------------------------------------------
 * WHEN                WHO                 WHY
 * 2006/10/03 02:48:11 yu.peng             new
 *------------------------------------------------------------------------------
 *
 ******************************************************************************/
/*******************/
/* PACKAGE SECTION */
/*******************/
package org.njo.webapp.root.model.dao;

/*******************/
/* IMPORT SECTION  */
/*******************/

import org.njo.webapp.root.utility.ObjectNotExistsException;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 数据访问对象.<br>
 * 访问数据库中表数据.
 * 提供以下几种方法:
 * <OL>
 * <LI><B>insert</B> 向数据库表中插入一条记录.</LI>
 * <LI><B>update</B> 更新数据库表中某条记录.</LI>
 * <LI><B>delete</B> 删除数据库表中指定主键的数据.</LI>
 * <LI><B>deleteAllRecords</B> 删除数据库表中所有的数据.</LI>
 * <LI><B>select</B> 从数据库表中取得指定主键的数据.</LI>
 * <LI><B>selectAllRecords(int,int)</B> 从数据库表中取得指定索引范围的所有数据.</LI>
 * <LI><B>selectAllRecords</B> 从数据库表中取得所有数据.</LI>
 * <LI><B>size</B> 从数据库表中取得所有数据的条数.</LI>
 * </OL>
 * <p/>
 * <pre>
 * 使用方法:
 *     Connection connection = null;
 *     UsersDAO usersDAO = null;
 * <p/>
 *     // open the database connection.
 *     try {
 *         connection = SystemContext.getDatabaseConnection();
 *     } catch (Throwable tw) {
 *         logger.error(tw);
 *         throw new ActivityError(tw);
 *     }
 * <p/>
 *     try {
 *         // access the database.
 *         // call the DAO Object insert the record into DB.
 *         usersDAO = new UsersDAO(connection);
 *         count = usersDAO.insert( new Users());
 * <p/>
 *         connection.commit();
 *     } catch (SQLException ex) {
 *         // when fail rollback the updated's data.
 *         try {
 *             connection.rollback();
 *         } catch (Throwable tw) {
 *             // when rollback fail,log the info.
 *             logger.error(tw);
 *         }
 *         logger.error(ex);
 *         throw new ActivityError(ex);
 *     } finally {
 *         try {
 *             if (connection != null) {
 *                 SystemContext.closeConnection(connection);
 *             }
 *         } catch (Throwable tw) {
 *             //
 *             logger.error(tw);
 *             throw new ActivityError(tw);
 *         }
 *     }
 * 注意:
 *     数据库连接对象(java.sql.Connection)的实例应由调用者创建,提供并操作,
 *     在本对象中不对数据库连接对象(java.sql.Connection)的实例进行任何操作,
 *     在本对象中不对任何的SQL异常处理,需要由调用者处理相应的SQL异常.
 * </pre>
 *
 * @author yu.peng
 * @version 1.00
 */
public class UsersDAO {

    /**
     * 数据库连接对象.
     */
    private Connection connection = null;

    /**
     * 构造函数.<br>
     * 需要一个数据库连接对象作为参数.
     *
     * @param argConnection 数据库连接对象
     */
    public UsersDAO(Connection argConnection) {
        connection = argConnection;
    }

    /**
     * 向数据库表中插入一条记录.<br>
     * 将argUsers中的数据插入到数据库中.
     *
     * @param name
     * @param password
     * @param description
     * @return 被插入数据的条数
     * @throws SQLException SQL异常
     */
    public int insert(String name, String password, String description) throws SQLException {
        StringBuffer insertSql = null;
        PreparedStatement insertStatement = null;
        try {
            insertSql = new StringBuffer();
            insertSql.append(" INSERT INTO USERS ( ");
            insertSql.append(" NAME , ");
            insertSql.append(" PASSWORD , ");
            insertSql.append(" DESCRIPTION ) ");
            insertSql.append(" VALUES ( ");
            insertSql.append(" ? , ");
            insertSql.append(" ? , ");
            insertSql.append(" ? ) ");

            insertStatement = connection.prepareStatement(insertSql.toString());
            insertStatement.setString(1, name);
            insertStatement.setString(2, password);
            insertStatement.setString(3, description);

            return insertStatement.executeUpdate();
        } finally {
            if (insertStatement != null) {
                insertStatement.close();
                insertStatement = null;
            }
        }
    }

    /**
     * 更新数据库表中某条记录.<br>
     * 根据argUsers中的主键,更新数据库中的数据,但是主键的字段不会被更新.
     * <p/>
     * TODO:根据具体的需要可能需要一些修改,如果已经修改了,请在这里加上注释,如果不需要
     * 修改,请删除这段TODO注释.
     *
     * @param name
     * @param password
     * @param description
     * @return updated 被更新数据的条数
     * @throws SQLException SQL异常
     */
    public int update(String name, String password, String description) throws SQLException {
        StringBuffer updateSql = null;
        PreparedStatement updateStatement = null;
        try {
            updateSql = new StringBuffer();
            updateSql.append(" UPDATE USERS SET ");
            updateSql.append(" PASSWORD = ? , ");
            updateSql.append(" DESCRIPTION = ? ");
            updateSql.append(" WHERE NAME = ? ");

            updateStatement = connection.prepareStatement(updateSql.toString());

            updateStatement.setString(1, password);
            updateStatement.setString(2, description);

            updateStatement.setString(3, name);
            return updateStatement.executeUpdate();
        } finally {
            if (updateStatement != null) {
                updateStatement.close();
                updateStatement = null;
            }
        }
    }

    /**
     * 更新数据库表中某条记录.<br>
     * 根据argUsers中的主键,更新数据库中的数据,但是主键的字段不会被更新.
     * <p/>
     * TODO:根据具体的需要可能需要一些修改,如果已经修改了,请在这里加上注释,如果不需要
     * 修改,请删除这段TODO注释.
     *
     * @param name
     * @param description
     * @return updated 被更新数据的条数
     * @throws SQLException SQL异常
     */
    public int update(String name, String description) throws SQLException {
        StringBuffer updateSql = null;
        PreparedStatement updateStatement = null;
        try {
            updateSql = new StringBuffer();
            updateSql.append(" UPDATE USERS SET ");
            updateSql.append(" DESCRIPTION = ? ");
            updateSql.append(" WHERE NAME = ? ");

            updateStatement = connection.prepareStatement(updateSql.toString());

            updateStatement.setString(1, description);

            updateStatement.setString(2, name);
            return updateStatement.executeUpdate();
        } finally {
            if (updateStatement != null) {
                updateStatement.close();
                updateStatement = null;
            }
        }
    }

    /**
     * 删除数据库表中指定主键的数据.<br>
     * 根据主键,删除数据库中的数据.
     * <p/>
     * TODO:根据具体的需要可能需要一些修改,如果已经修改了,请在这里加上注释,如果不需要
     * 修改,请删除这段TODO注释.
     *
     * @param argName String
     * @return 被删除数据的条数
     * @throws SQLException SQL异常
     */
    public int delete(String argName) throws SQLException {
//    public int delete(  ) throws SQLException {
        StringBuffer deleteSql = null;
        PreparedStatement deleteStatement = null;
        try {
            deleteSql = new StringBuffer();
            deleteSql.append(" DELETE FROM USERS ");
            deleteSql.append(" WHERE NAME = ? ");

            deleteStatement = connection.prepareStatement(deleteSql.toString());

            deleteStatement.setString(1, argName);

            return deleteStatement.executeUpdate();
        } finally {
            if (deleteStatement != null) {
                deleteStatement.close();
                deleteStatement = null;
            }
        }
    }

    /**
     * 删除数据库表中所有的数据.<br>
     *
     * @return 被删除数据的条数
     * @throws SQLException SQL异常
     */
    public int deleteAllRecords() throws SQLException {
        StringBuffer deleteSql = null;
        PreparedStatement deleteStatement = null;
        try {
            deleteSql = new StringBuffer();
            deleteSql.append(" DELETE FROM USERS ");

            deleteStatement = connection.prepareStatement(deleteSql.toString());
            return deleteStatement.executeUpdate();
        } finally {
            if (deleteStatement != null) {
                deleteStatement.close();
                deleteStatement = null;
            }
        }
    }

    /**
     * 从数据库表中取得指定主键的数据.<br>
     * 根据主键,从数据库中取得数据.
     * <p/>
     * TODO:根据具体的需要可能需要一些修改,如果已经修改了,请在这里加上注释,如果不需要
     * 修改,请删除这段TODO注释.
     * <p/>
     * <pre>
     * 注意:如果指定的条件没有取得到数据时,将会抛出一个RecordNotFoundException异常,
     *      应该在调用这个方法的地方处理这个异常.
     * </pre>
     *
     * @param argName String
     * @return 取得的数据对象实例
     * @throws SQLException             SQL异常
     * @throws ObjectNotExistsException 没有找到数据
     */
    public String[] auth(String argName) throws SQLException, ObjectNotExistsException {
        StringBuffer selectSql = null;
        PreparedStatement selectStatement = null;
        ResultSet resultSet = null;
        String[] user = null;
        try {
            selectSql = new StringBuffer();
            selectSql.append(" SELECT NAME , ");
            selectSql.append("        PASSWORD ");
            selectSql.append(" FROM USERS ");
            selectSql.append(" WHERE NAME = ? ");

            selectStatement = connection.prepareStatement(
                    selectSql.toString(),
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);

            selectStatement.setString(1, argName);

            resultSet = selectStatement.executeQuery();

            if (resultSet.next()) {
                user = new String[2];
                user[0] = resultSet.getString(1);
                user[1] = resultSet.getString(2);

            } else {
                throw new ObjectNotExistsException("not found data in database.");
            }
        } finally {
            if (resultSet != null) {
                resultSet.close();
                resultSet = null;
            }
            if (selectStatement != null) {
                selectStatement.close();
                selectStatement = null;
            }
        }
        return user;
    }

    /**
     * @param argStartIndex
     * @param argEndIndex
     * @return
     * @throws java.sql.SQLException
     */
    public List selectAllRecords(int argStartIndex, int argEndIndex) throws SQLException {
        StringBuffer selectSql = null;
        PreparedStatement selectStatement = null;
        ResultSet resultSet = null;
        List resultList = new ArrayList();
        int curIndex = 0;
        try {
            selectSql = new StringBuffer();
            selectSql.append(" SELECT NAME , ");
            selectSql.append("        PASSWORD , ");
            selectSql.append("        DESCRIPTION ");
            selectSql.append(" FROM USERS ");
            selectStatement = connection.prepareStatement(
                    selectSql.toString(),
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY
            );
            resultSet = selectStatement.executeQuery();
            if (resultSet.absolute(argStartIndex)) {
                curIndex = argStartIndex;
                do {
                    String[] user = new String[3];
                    user[0] = resultSet.getString(1);
                    user[1] = resultSet.getString(2);
                    user[2] = resultSet.getString(3);
                    resultList.add(user);
                    curIndex++;
                }
                while (resultSet.next() && (curIndex <= argEndIndex));
            }
        }
        finally {
            if (resultSet != null) {
                resultSet.close();
                resultSet = null;
            }
            if (selectStatement != null) {
                selectStatement.close();
                selectStatement = null;
            }
        }
        return resultList;
    }

    /**
     * @return
     * @throws java.sql.SQLException
     */
    public List selectAllRecords()
            throws SQLException {
        return selectAllRecords(1, Integer.MAX_VALUE);
    }

    /**
     * @param argName
     * @return
     * @throws java.sql.SQLException
     */
    public String[] selectByName(String argName)
            throws SQLException, ObjectNotExistsException {
        StringBuffer selectSql = null;
        PreparedStatement selectStatement = null;
        ResultSet resultSet = null;
        String[] user = null;
        try {
            selectSql = new StringBuffer();
            selectSql.append(" SELECT NAME , ");
            selectSql.append("        PASSWORD , ");
            selectSql.append("        DESCRIPTION ");
            selectSql.append(" FROM USERS ");
            selectSql.append(" WHERE NAME = ? ");
            selectStatement = connection.prepareStatement(
                    selectSql.toString(),
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY
            );

            selectStatement.setString(1, argName);

            resultSet = selectStatement.executeQuery();

            if (resultSet.next()) {
                user = new String[3];
                user[0] = resultSet.getString(1);
                user[1] = resultSet.getString(2);
                user[2] = resultSet.getString(3);
            } else {
                throw new ObjectNotExistsException("not found data in database.");
            }
        }
        finally {
            if (resultSet != null) {
                resultSet.close();
                resultSet = null;
            }
            if (selectStatement != null) {
                selectStatement.close();
                selectStatement = null;
            }
        }
        return user;
    }


}

